
USE [ppjdb]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspMoveSubTree]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspMoveSubTree]
GO

CREATE PROCEDURE dbo.[uspMoveSubTree]
	@biOldParentID [bigint]
	,@biNewParentID [bigint]
	,@iRetCode [int] output
AS
BEGIN
 begin try
	DECLARE @hiOld hierarchyid
		,@hiNew hierarchyid
			
	SELECT @hiOld = StoreNode 
		FROM dbo.tblStore (nolock) 
		WHERE StoreID = @biOldParentID ;

	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
	BEGIN TRANSACTION
	
		SELECT @hiNew = StoreNode 
			FROM dbo.tblStore (nolock)
			WHERE StoreID = @biNewParentID ;

		SELECT @hiNew = @hiNew.GetDescendant(max(StoreNode), NULL) 
			FROM dbo.tblStore (nolock) 
			WHERE StoreNode.GetAncestor(1)=@hiNew ;

		UPDATE dbo.tblStore  
			SET StoreNode = StoreNode.GetReparentedValue(@hiOld, @hiNew)
			WHERE StoreNode.IsDescendantOf(@hiOld) = 1 ; 

	COMMIT TRANSACTION
	set @iRetCode = 0
 end try
 begin catch
	set @iRetCode = -1
	ROLLBACK TRAN 
 end catch	
END ;
GO

